Orecle Fusion - Query for GL to Project Cost
|
4 min read
AP
SELECT
gjh.JE_HEADER_ID hdr_id,
gjl.JE_LINE_NUM line_num,
--xte.source_id_int_1,
sql1.project,
sql1.project_name,
sql1.task,
sql1.task_name,
aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjl.ENTERED_DR "Entered DR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
gcc.SEGMENT1||'-'||gcc.SEGMENT2||'-'
||gcc.SEGMENT3||'-'||gcc.SEGMENT4||'-'
||gcc.SEGMENT5||'-'||gcc.SEGMENT6||'-'
||gcc.SEGMENT7||'-'||gcc.SEGMENT8 "Code Combination",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
aia.VENDOR_ID "Vendor Id",
-- aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name"
FROM
ap_invoices_all aia,
ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
xla_ae_headers XAH,
xla_ae_lines XAL,
GL_IMPORT_REFERENCES gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
POZ_SUPPLIERS aps,
xla_distribution_links XDL,
(select aid1.invoice_distribution_id,
pa.project_id,
nvl(pa.segment1,'NO PROJECT') Project,
pn.name PROJECT_NAME,
pt.proj_element_id task_id,
nvl(pt.element_number,'NO TASK') Task,
pt.name TASK_NAME
from ap_invoice_distributions_all aid1,
pjf_projects_all_b pa,
PJF_PROJECTS_ALL_TL pn,
pjf_proj_elements_vl pt
where 1=1
and aid1.pjc_project_id=pa.project_id(+)
and pa.project_id = pn.project_id (+)
and aid1.pjc_task_id=pt.proj_element_id(+)
and pn.language = 'US') sql1
WHERE 1=1
and aida.invoice_distribution_id=sql1.invoice_distribution_id
and XAH.ae_header_id = XAL.ae_header_id
and XAH.je_category_name = 'Purchase Invoices'
and XAH.gl_transfer_status_code= 'Y'
and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and aia.VENDOR_ID=aps.VENDOR_ID
and gjh.STATUS='P'
and gjh.Actual_flag='A'
--and gjh.CURRENCY_CODE='USD'
and aida.invoice_id = aia.invoice_id
------
and XDL.source_distribution_type = 'AP_INV_DIST'
and XDL.applied_to_application_id = 200
and XDL.source_distribution_id_num_1 = aida.invoice_distribution_id
and aila.invoice_id = aia.invoice_id
and aida.invoice_id = aila.invoice_id
and aida.invoice_line_number = aila.line_number
--and aia.invoice_id = 300000005842424
and AILA.line_type_lookup_code != 'LIABILITY'
and aia.invoice_num = '122321474'
and aida.cancellation_flag = 'N'
AND NVL(AIDA.REVERSAL_FLAG,'?') !='Y'
AND XAL.ae_header_id = XDL.ae_header_id
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUMGeneral
SELECT count(1) FROM
(select
LedgerSetPVO.*,
JournalLinePVO.*,
SRC.*,
CURR.*,
GCC.*,
LedgerPVO.*,
AccountBIVO.*,
JournalImportReferencePVO.*,
JournalLinePVO.JE_HEADER_ID hdr_id,
JournalLinePVO.JE_LINE_NUM line_num
from
(SELECT /*+ qb_name(JournalLinePVO) */ BatchApprovedBy.PERSON_NAME_ID AS PERSON_NAME_ID_L, BatchApprovedBy.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE_L, BatchApprovedBy.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE_L, BatchApprovedBy.DISPLAY_NAME AS DISPLAY_NAME_B, XleEntityProfiles.NAME AS LE_NAME1, XleEntityProfiles.LEGAL_ENTITY_ID AS LEGAL_ENTITY_ID1, JrnlLine.ACCOUNTED_CR, JrnlLine.ACCOUNTED_DR, JrnlLine.CODE_COMBINATION_ID, JrnlLine.CURRENCY_CODE, JrnlLine.DESCRIPTION, JrnlLine.EFFECTIVE_DATE, JrnlLine.ENTERED_CR, JrnlLine.ENTERED_DR, JrnlLine.JE_HEADER_ID, JrnlLine.JE_LINE_NUM, JrnlLine.LEDGER_ID AS LEDGER_ID371, JrnlLine.LINE_TYPE_CODE, JrnlLine.PERIOD_NAME AS PERIOD_NAME401, JrnlLine.REFERENCE_1, JrnlLine.REFERENCE_10, JrnlLine.REFERENCE_2, JrnlLine.REFERENCE_3, JrnlLine.REFERENCE_4, JrnlLine.REFERENCE_5, JrnlLine.REFERENCE_6, JrnlLine.REFERENCE_7, JrnlLine.REFERENCE_8, JrnlLine.REFERENCE_9, JrnlLine.STAT_AMOUNT, JrnlHdr.CREATED_BY AS CREATED_BY1, JrnlHdr.DATE_CREATED, JrnlHdr.DEFAULT_EFFECTIVE_DATE, JrnlHdr.DESCRIPTION AS DESCRIPTION1, JrnlHdr.EXTERNAL_REFERENCE, JrnlHdr.JE_FROM_SLA_FLAG, JrnlHdr.JE_HEADER_ID AS JE_HEADER_ID1, JrnlHdr.JE_SOURCE, JrnlHdr.LAST_UPDATE_DATE AS LAST_UPDATE_DATE1, JrnlHdr.LAST_UPDATED_BY AS LAST_UPDATED_BY1, JrnlHdr.MULTI_CURRENCY_FLAG, JrnlHdr.NAME, JrnlHdr.ORIGINATING_BAL_SEG_VALUE, JrnlHdr.PARENT_JE_HEADER_ID, JrnlHdr.PERIOD_NAME AS PERIOD_NAME1, JrnlHdr.REFERENCE_DATE, JrnlHdr.STATUS AS STATUS1, JrnlBatch.ACCOUNTED_PERIOD_TYPE, JrnlBatch.APPROVAL_STATUS_CODE, JrnlBatch.CREATED_BY AS CREATED_BY2, JrnlBatch.CREATION_DATE AS CREATION_DATE2, JrnlBatch.DESCRIPTION AS DESCRIPTION2, JrnlBatch.GROUP_ID, JrnlBatch.JE_BATCH_ID AS JE_BATCH_ID1, JrnlBatch.LAST_UPDATE_DATE AS LAST_UPDATE_DATE2, JrnlBatch.LAST_UPDATED_BY AS LAST_UPDATED_BY2, JrnlBatch.NAME AS NAME1, JrnlBatch.POSTED_DATE AS POSTED_DATE1, Ledger.CHART_OF_ACCOUNTS_ID AS L_CHART_OF_ACCOUNTS_ID, CreatedByPersonName.DISPLAY_NAME AS DISPLAY_NAME03, CreatedByPersonName.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE03, CreatedByPersonName.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE03, CreatedByPersonName.PERSON_NAME_ID AS PERSON_NAME_ID03, LastUpdatedByPersonName.DISPLAY_NAME AS DISPLAY_NAME04, LastUpdatedByPersonName.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE04, LastUpdatedByPersonName.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE04, LastUpdatedByPersonName.PERSON_NAME_ID AS PERSON_NAME_ID04, (GL_LEDGER_INFO.get_unit_of_measure(Ledger.CHART_OF_ACCOUNTS_ID,JrnlLine.CODE_COMBINATION_ID)) AS UNIT_OF_MEASURE
FROM GL_JE_LINES JrnlLine, GL_JE_HEADERS JrnlHdr, GL_JE_BATCHES JrnlBatch, GL_LEDGERS Ledger, XLE_ENTITY_PROFILES XleEntityProfiles, PER_USERS CreatedByUser, PER_USERS LastUpdatedByUser, PER_PERSON_NAMES_F_V CreatedByPersonName, PER_PERSON_NAMES_F_V LastUpdatedByPersonName, PER_PERSON_NAMES_F_V BatchApprovedBy
WHERE (JrnlLine.JE_HEADER_ID = JrnlHdr.JE_HEADER_ID AND JrnlHdr.JE_BATCH_ID = JrnlBatch.JE_BATCH_ID AND JrnlLine.LEDGER_ID = Ledger.LEDGER_ID(+) AND JrnlHdr.LEGAL_ENTITY_ID = XleEntityProfiles.LEGAL_ENTITY_ID(+) AND JrnlHdr.CREATED_BY = CreatedByUser.USERNAME(+) AND ('Y') = CreatedByUser.ACTIVE_FLAG(+) AND JrnlHdr.LAST_UPDATED_BY = LastUpdatedByUser.USERNAME(+) AND ('Y') = LastUpdatedByUser.ACTIVE_FLAG(+) AND CreatedByUser.PERSON_ID = CreatedByPersonName.PERSON_ID(+) AND LastUpdatedByUser.PERSON_ID = LastUpdatedByPersonName.PERSON_ID(+) AND JrnlBatch.APPROVER_EMPLOYEE_ID = BatchApprovedBy.PERSON_ID(+) AND ( DATE'2024-03-26' BETWEEN CreatedByPersonName.EFFECTIVE_START_DATE(+) AND CreatedByPersonName.EFFECTIVE_END_DATE(+)) AND ( DATE'2024-03-26' BETWEEN LastUpdatedByPersonName.EFFECTIVE_START_DATE(+) AND LastUpdatedByPersonName.EFFECTIVE_END_DATE(+)) AND ( DATE'2024-03-26' BETWEEN BatchApprovedBy.EFFECTIVE_START_DATE(+) AND BatchApprovedBy.EFFECTIVE_END_DATE(+))) AND ( ( JrnlLine.ledger_id in ( 300000003546929,300000003546923,300000003870645,300000003546935,300000003870653,300000003546926,300000003870649,300000003546932,300000003870657 ) ))) JournalLinePVO,
(SELECT /*+ qb_name(LedgerPVO) */ PersonUpdatedBy.PERSON_NAME_ID AS PU_PERSON_NAME_ID, PersonUpdatedBy.EFFECTIVE_START_DATE AS PU_EFFECTIVE_START_DATE, PersonUpdatedBy.EFFECTIVE_END_DATE AS PU_EFFECTIVE_END_DATE, PersonUpdatedBy.DISPLAY_NAME AS PU_DISPLAY_NAME, PersonCreatedBy.DISPLAY_NAME AS PC_DISPLAY_NAME, PersonCreatedBy.EFFECTIVE_START_DATE AS PC_EFFECTIVE_START_DATE, PersonCreatedBy.EFFECTIVE_END_DATE AS PC_EFFECTIVE_END_DATE, PersonCreatedBy.PERSON_NAME_ID AS PC_PERSON_NAME_ID, Ledger.CHART_OF_ACCOUNTS_ID, Ledger.CREATED_BY, Ledger.CREATION_DATE, Ledger.CURRENCY_CODE AS CURRENCY_CODE289, Ledger.DESCRIPTION AS DESCRIPTION303, Ledger.LAST_UPDATE_DATE, Ledger.LAST_UPDATED_BY, Ledger.LEDGER_CATEGORY_CODE, Ledger.LEDGER_ID, Ledger.NAME AS NAME496, Ledger.PERIOD_SET_NAME AS PERIOD_SET_NAME541, Ledger.SLA_ACCOUNTING_METHOD_CODE, Ledger.SUSPENSE_ALLOWED_FLAG, KeyFlexfieldStructureInstanc.NAME AS NAME1050, KeyFlexfieldStructureInstanc.STRUCTURE_INSTANCE_ID, Ledger.INTERCO_GAIN_LOSS_CCID, AcctgMethodTrans.ACCOUNTING_METHOD_TYPE_CODE AS ACCOUNTING_METHOD_TYPE_CODE1, AcctgMethodTrans.ACCOUNTING_METHOD_CODE AS ACCOUNTING_METHOD_CODE1, AcctgMethodTrans.LANGUAGE AS ACCT_MTHD_TRANS_LANGUAGE, AcctgMethodTrans.NAME
FROM GL_LEDGERS Ledger, FND_KF_STR_INSTANCES_VL KeyFlexfieldStructureInstanc, XLA_ACCTG_METHODS_B AcctgMethod, XLA_ACCTG_METHODS_TL AcctgMethodTrans, PER_USERS UserCreatedBy, PER_USERS UserUpdatedBy, PER_PERSON_NAMES_F_V PersonCreatedBy, PER_PERSON_NAMES_F_V PersonUpdatedBy
WHERE (Ledger.CHART_OF_ACCOUNTS_ID = KeyFlexfieldStructureInstanc.STRUCTURE_INSTANCE_NUMBER AND (101) = KeyFlexfieldStructureInstanc.APPLICATION_ID AND ('GL#') = KeyFlexfieldStructureInstanc.KEY_FLEXFIELD_CODE AND Ledger.SLA_ACCOUNTING_METHOD_CODE = AcctgMethod.ACCOUNTING_METHOD_CODE(+) AND Ledger.SLA_ACCOUNTING_METHOD_TYPE = AcctgMethod.ACCOUNTING_METHOD_TYPE_CODE(+) AND AcctgMethod.ACCOUNTING_METHOD_TYPE_CODE = AcctgMethodTrans.ACCOUNTING_METHOD_TYPE_CODE(+) AND AcctgMethod.ACCOUNTING_METHOD_CODE = AcctgMethodTrans.ACCOUNTING_METHOD_CODE(+) AND (USERENV('LANG')) = AcctgMethodTrans.LANGUAGE(+) AND Ledger.CREATED_BY = UserCreatedBy.USERNAME(+) AND ('Y') = UserCreatedBy.ACTIVE_FLAG(+) AND Ledger.LAST_UPDATED_BY = UserUpdatedBy.USERNAME(+) AND ('Y') = UserUpdatedBy.ACTIVE_FLAG(+) AND UserCreatedBy.PERSON_ID = PersonCreatedBy.PERSON_ID(+) AND UserUpdatedBy.PERSON_ID = PersonUpdatedBy.PERSON_ID(+) AND ( DATE'2024-03-26' BETWEEN PersonCreatedBy.EFFECTIVE_START_DATE(+) AND PersonCreatedBy.EFFECTIVE_END_DATE(+)) AND ( DATE'2024-03-26' BETWEEN PersonUpdatedBy.EFFECTIVE_START_DATE(+) AND PersonUpdatedBy.EFFECTIVE_END_DATE(+))) AND ( ( (Ledger.OBJECT_TYPE_CODE = 'L' ) ) )) LedgerPVO,
(SELECT /*+ qb_name(LedgerSetPVO) */ PersonUpdatedBy.PERSON_NAME_ID AS PU_PERSON_NAME_ID, PersonUpdatedBy.EFFECTIVE_START_DATE AS PU_EFFECTIVE_START_DATE, PersonUpdatedBy.EFFECTIVE_END_DATE AS PU_EFFECTIVE_END_DATE, PersonUpdatedBy.DISPLAY_NAME AS PU_DISPLAY_NAME, PersonCreatedBy.DISPLAY_NAME AS PC_DISPLAY_NAME, PersonCreatedBy.EFFECTIVE_START_DATE AS PC_EFFECTIVE_START_DATE, PersonCreatedBy.EFFECTIVE_END_DATE AS PC_EFFECTIVE_END_DATE, PersonCreatedBy.PERSON_NAME_ID AS PC_PERSON_NAME_ID, Ledgers.CREATED_BY AS CREATED_BY302, Ledgers.LAST_UPDATE_DATE AS LAST_UPDATE_DATE447, Ledgers.LAST_UPDATED_BY AS LAST_UPDATED_BY464, Ledgers.LEDGER_ID AS LEDGER_ID510, LedgerSetAssignment.LEDGER_SET_ID AS LSA_LEDGER_SET_ID, LedgerSetAssignment.LEDGER_ID AS LSA_LEDGER_ID, LedgerSetAssignment.START_DATE AS LSA_START_DATE, LedgerSetAssignment.END_DATE AS LSA_END_DATE, LedgerSet.LEDGER_ID AS LEDGER_ID1, LedgerSet.NAME AS NAME1312, LedgerSet.CREATION_DATE AS CREATION_DATE1782
FROM GL_LEDGERS Ledgers, GL_LEDGER_SET_ASSIGNMENTS LedgerSetAssignment, GL_LEDGERS LedgerSet, PER_USERS UserCreatedBy, PER_USERS UserUpdatedBy, PER_PERSON_NAMES_F_V PersonCreatedBy, PER_PERSON_NAMES_F_V PersonUpdatedBy
WHERE Ledgers.LEDGER_ID = LedgerSetAssignment.LEDGER_ID AND LedgerSetAssignment.LEDGER_SET_ID = LedgerSet.LEDGER_ID(+) AND LedgerSet.CREATED_BY = UserCreatedBy.USERNAME(+) AND ('Y') = UserCreatedBy.ACTIVE_FLAG(+) AND LedgerSet.LAST_UPDATED_BY = UserUpdatedBy.USERNAME(+) AND ('Y') = UserUpdatedBy.ACTIVE_FLAG(+) AND UserCreatedBy.PERSON_ID = PersonCreatedBy.PERSON_ID(+) AND UserUpdatedBy.PERSON_ID = PersonUpdatedBy.PERSON_ID(+) AND ( DATE'2024-03-26' BETWEEN PersonCreatedBy.EFFECTIVE_START_DATE(+) AND PersonCreatedBy.EFFECTIVE_END_DATE(+)) AND ( DATE'2024-03-26' BETWEEN PersonUpdatedBy.EFFECTIVE_START_DATE(+) AND PersonUpdatedBy.EFFECTIVE_END_DATE(+))) LedgerSetPVO,
(SELECT JrnlSrc.JE_SOURCE_NAME, JrnlSrcTransLang.DESCRIPTION AS LANG_DESCRIPTION, JrnlSrcTransLang.JE_SOURCE_NAME AS LANG_JE_SOURCE_NAME1, JrnlSrcTransLang.LANGUAGE AS LANG_LANGUAGE, JrnlSrcTransLang.USER_JE_SOURCE_NAME AS LANG_USER_JE_SOURCE_NAME
FROM GL_JE_SOURCES_B JrnlSrc, GL_JE_SOURCES_TL JrnlSrcTransLang
WHERE JrnlSrc.JE_SOURCE_NAME = JrnlSrcTransLang.JE_SOURCE_NAME AND (userenv('LANG')) = JrnlSrcTransLang.LANGUAGE) SRC,
(SELECT CurrenciesBPEO.CURRENCY_CODE, CurrenciesBPEO.DIGITAL_CURRENCY_CODE
FROM FND_CURRENCIES_B CurrenciesBPEO) CURR,
(SELECT /*+ qb_name(AccountBIVO) */ BIFlexfieldEO.CODE_COMBINATION_ID AS s_g_0, BIFlexfieldEO.CHART_OF_ACCOUNTS_ID AS s_g_1, BIFlexfieldEO.SEGMENT1,BIFlexfieldEO.SEGMENT2,BIFlexfieldEO.SEGMENT3,BIFlexfieldEO.SEGMENT4,BIFlexfieldEO.SEGMENT5,BIFlexfieldEO.SEGMENT6,BIFlexfieldEO.SEGMENT7,BIFlexfieldEO.SEGMENT8,BIFlexfieldEO.SEGMENT9
FROM GL_CODE_COMBINATIONS BIFlexfieldEO) GCC,
(SELECT /*+ qb_name(AccountBIVO) */ BIFlexfieldEO.CODE_COMBINATION_ID AS s_g_0,
BIFlexfieldEO.CHART_OF_ACCOUNTS_ID AS s_g_1, (decode(BIFlexfieldEO.CHART_OF_ACCOUNTS_ID,2001,BIFlexfieldEO.SEGMENT1||'-'||BIFlexfieldEO.SEGMENT2||'-'||BIFlexfieldEO.SEGMENT3||'-'||BIFlexfieldEO.SEGMENT4||'-'||BIFlexfieldEO.SEGMENT5||'-'||BIFlexfieldEO.SEGMENT6||'-'||BIFlexfieldEO.SEGMENT7||'-'||BIFlexfieldEO.SEGMENT8||'-'||BIFlexfieldEO.SEGMENT9,null)) AS CONCAT_VALUES,
BIFlexfieldEO.SEGMENT1,BIFlexfieldEO.SEGMENT2,BIFlexfieldEO.SEGMENT3,BIFlexfieldEO.SEGMENT4,BIFlexfieldEO.SEGMENT5,BIFlexfieldEO.SEGMENT6,BIFlexfieldEO.SEGMENT7,BIFlexfieldEO.SEGMENT8,BIFlexfieldEO.SEGMENT9
FROM GL_CODE_COMBINATIONS BIFlexfieldEO) AccountBIVO,
(SELECT /*+ qb_name(JournalImportReferencePVO) */ GlImportReferences.JE_HEADER_ID, GlImportReferences.JE_LINE_NUM, GlImportReferences.GL_SL_LINK_ID, GlImportReferences.GL_SL_LINK_TABLE
FROM GL_IMPORT_REFERENCES GlImportReferences
WHERE ( (GlImportReferences.GL_SL_LINK_ID IS NOT NULL ) ) ) JournalImportReferencePVO
WHERE JournalLinePVO.LEDGER_ID371 = LedgerPVO.LEDGER_ID
AND LedgerPVO.LEDGER_ID = LedgerSetPVO.LEDGER_ID510(+)
AND JournalLinePVO.JE_SOURCE = SRC.JE_SOURCE_NAME
AND JournalLinePVO.CURRENCY_CODE = CURR.CURRENCY_CODE
AND JournalLinePVO.CODE_COMBINATION_ID = GCC.s_g_0
AND JournalLinePVO.L_CHART_OF_ACCOUNTS_ID = GCC.s_g_1
AND LedgerPVO.INTERCO_GAIN_LOSS_CCID = AccountBIVO.s_g_0(+)
AND LedgerPVO.CHART_OF_ACCOUNTS_ID = AccountBIVO.s_g_1(+)
AND JournalLinePVO.JE_HEADER_ID = JournalImportReferencePVO.JE_HEADER_ID(+)
AND JournalLinePVO.JE_LINE_NUM = JournalImportReferencePVO.JE_LINE_NUM(+)
) gl_vanila,
(
SELECT
gjh.JE_HEADER_ID hdr_id,
gjl.JE_LINE_NUM line_num,
sql1.project,
sql2.task,
aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjl.ENTERED_DR "Entered DR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
gcc.SEGMENT1||'-'||gcc.SEGMENT2||'-'
||gcc.SEGMENT3||'-'||gcc.SEGMENT4||'-'
||gcc.SEGMENT5||'-'||gcc.SEGMENT6||'-'
||gcc.SEGMENT7||'-'||gcc.SEGMENT8 "Code Combination",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
aia.VENDOR_ID "Vendor Id",
-- aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name"
FROM
ap_invoices_all aia,
xla_transaction_entities XTE,
xla_events xev,
xla_ae_headers XAH,
xla_ae_lines XAL,
GL_IMPORT_REFERENCES gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
POZ_SUPPLIERS aps,
(select aid1.invoice_id,
pa.project_id,
nvl(pa.segment1,'NO PROJECT') Project
from ap_invoice_distributions_all aid1,
pjf_projects_all_b pa
where aid1.rowid in
(select MAx(rowid)
from ap_invoice_distributions_all aid2
where aid1.INvoice_ID=aid2.INvoice_ID
group by aid1.invoice_id)
and aid1.pjc_project_id=pa.project_id(+)) sql1,
(select aid1.invoice_id,
pt.proj_element_id task_id,
nvl(pt.element_number,'NO TASK') Task
from ap_invoice_distributions_all aid1,
pjf_proj_elements_vl pt
where aid1.rowid in
(select MAx(rowid)
from ap_invoice_distributions_all aid2
where aid1.INvoice_ID=aid2.INvoice_ID
group by aid1.invoice_id)
and aid1.pjc_task_id=pt.proj_element_id(+)) sql2
WHERE
aia.INVOICE_ID = xte.source_id_int_1
and aia.INVOICE_ID=sql1.Invoice_ID
and aia.INVOICE_ID=sql2.Invoice_ID
and xev.entity_id= xte.entity_id
and xah.entity_id= xte.entity_id
and xah.event_id= xev.event_id
and XAH.ae_header_id = XAL.ae_header_id
and XAH.je_category_name = 'Purchase Invoices'
and XAH.gl_transfer_status_code= 'Y'
and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and aia.VENDOR_ID=aps.VENDOR_ID
and gjh.STATUS='P'
and gjh.Actual_flag='A'
--and gjh.CURRENCY_CODE='USD'
and xal.ACCOUNTING_CLASS_CODE != 'LIABILITY'
) added_data
where added_data.hdr_id(+) = gl_vanila.hdr_id
and added_data.line_num(+) = gl_vanila.line_num
--85745